package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Teacher;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class TeacherRepository {
	
	public static Teacher get(String email) {
        
	    try {
            String sql = "select teacher_id, first_name, last_name, email from teachers where email = ?";
           
            Teacher teacher = null;
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                       
                    ps.setString(1, email.toLowerCase());
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                            teacher = new Teacher();
                            teacher.id = resultSet.getInt(1);
                            teacher.firstName = resultSet.getString(2);
                            teacher.lastName = resultSet.getString(3);
                            teacher.email = resultSet.getString(4);
                        }
                    }
                }
            }
           
            return teacher;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }              
	}
	
	public static void add(String email, String firstName, String lastName) {				 
					
		 if (null != get(email))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "[" + email + "] is already present");
		 
		 try {
		     String sql = "insert into teachers values (0, ?, ?, ?)";                      		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {		                
		             ps.setString(1, email.toLowerCase());
		             ps.setString(2, firstName.toLowerCase());
		             ps.setString(3, lastName.toLowerCase());
		            
		             ps.executeUpdate();                                    
		         }
		     }		       
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }              
	 }	

	 public static ArrayList<Teacher> getAll() {
		try {
	        String sql = "select teacher_id, email, first_name, last_name from teachers order by first_name, last_name";
	       
	        ArrayList<Teacher> teachers = new ArrayList<>();
	       
	        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {	               	        	
	            try (PreparedStatement ps = connection.prepareStatement(sql)) {	                   	               	               
	                try (ResultSet resultSet = ps.executeQuery()) {                                        
	                    while (resultSet.next()) {
	                        Teacher teacher = new Teacher();
	                        teacher.id = resultSet.getInt(1);
	                        teacher.email = resultSet.getString(2);
	                        teacher.firstName = resultSet.getString(3);
	                        teacher.lastName = resultSet.getString(4);	                       
	                        
	                        teachers.add(teacher);
	                    }
	                }
	            }
	        }
	       
	        return teachers;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    } 
	}
}

